using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Configuration;
using NLog;

namespace MT5Trade.Tests.TestFramework
{
    /// <summary>
    /// 测试数据种子器 - 用于初始化测试数据库
    /// 遵循 generic-rule.md 规则 #289：所有测试数据必须从MySQL获取
    /// </summary>
    public class TestDataSeeder
    {
        private static readonly Logger Logger = LogManager.GetCurrentClassLogger();
        private readonly string _connectionString;
        private readonly bool _cleanupFirst;

        public TestDataSeeder(string connectionString, bool cleanupFirst = true)
        {
            _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
            _cleanupFirst = cleanupFirst;
        }

        /// <summary>
        /// 执行数据库初始化
        /// 复杂度: O(n) where n = 测试场景数量
        /// </summary>
        public async Task<bool> InitializeAsync()
        {
            try
            {
                Logger.Info("开始初始化测试数据库");

                if (_cleanupFirst)
                {
                    await CleanupAsync();
                }

                // 调用存储过程初始化数据
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                using var command = new MySqlCommand("CALL sp_initialize_bonus_arbitrage_test_data()", connection);
                command.CommandTimeout = 60; // 60秒超时

                await command.ExecuteNonQueryAsync();

                // 验证数据是否正确初始化
                var rowCount = await GetRowCountAsync(connection);

                Logger.Info($"测试数据初始化完成，共插入 {rowCount} 条测试场景");
                return rowCount > 0;
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "初始化测试数据失败");
                return false;
            }
        }

        /// <summary>
        /// 清理测试数据
        /// </summary>
        public async Task CleanupAsync()
        {
            try
            {
                Logger.Info("清理现有测试数据");

                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                // 调用清理存储过程
                using var command = new MySqlCommand("CALL sp_cleanup_test_data()", connection);
                await command.ExecuteNonQueryAsync();

                Logger.Info("测试数据清理完成");
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "清理测试数据失败");
                throw;
            }
        }

        /// <summary>
        /// 插入自定义测试场景
        /// </summary>
        public async Task<int> InsertTestScenarioAsync(
            string scenarioName,
            double mainLossProbability,
            double expectedMove,
            double spread,
            double bonusRemaining,
            double followerMarginLevel,
            double currentVolatility,
            double expectedGap,
            string marketCondition,
            string notes = null)
        {
            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                var sql = @"
                    INSERT INTO bonus_arbitrage_test_scenarios
                    (scenario_name, main_loss_probability, expected_move, spread,
                     bonus_remaining, follower_margin_level, current_volatility,
                     expected_gap, market_condition, notes, created_at)
                    VALUES
                    (@name, @loss_prob, @move, @spread, @bonus, @margin,
                     @volatility, @gap, @market, @notes, NOW())";

                using var command = new MySqlCommand(sql, connection);
                command.Parameters.AddWithValue("@name", scenarioName);
                command.Parameters.AddWithValue("@loss_prob", mainLossProbability);
                command.Parameters.AddWithValue("@move", expectedMove);
                command.Parameters.AddWithValue("@spread", spread);
                command.Parameters.AddWithValue("@bonus", bonusRemaining);
                command.Parameters.AddWithValue("@margin", followerMarginLevel);
                command.Parameters.AddWithValue("@volatility", currentVolatility);
                command.Parameters.AddWithValue("@gap", expectedGap);
                command.Parameters.AddWithValue("@market", marketCondition);
                command.Parameters.AddWithValue("@notes", notes ?? (object)DBNull.Value);

                await command.ExecuteNonQueryAsync();

                // 返回插入的ID
                command.CommandText = "SELECT LAST_INSERT_ID()";
                var result = await command.ExecuteScalarAsync();
                return Convert.ToInt32(result);
            }
            catch (Exception ex)
            {
                Logger.Error(ex, $"插入测试场景失败: {scenarioName}");
                throw;
            }
        }

        /// <summary>
        /// 批量插入历史交易数据
        /// </summary>
        public async Task BulkInsertTradeHistoryAsync(List<(string Symbol, DateTime TradeTime,
            bool IsWin, double PnL, double Spread, double Gap, double Volatility)> trades)
        {
            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                using var transaction = await connection.BeginTransactionAsync();

                try
                {
                    var sql = @"
                        INSERT INTO arbitrage_trade_history
                        (symbol, trade_time, is_win, pnl, spread, gap_used, volatility_at_trade)
                        VALUES
                        (@symbol, @time, @win, @pnl, @spread, @gap, @volatility)";

                    foreach (var trade in trades)
                    {
                        using var command = new MySqlCommand(sql, connection, transaction);
                        command.Parameters.AddWithValue("@symbol", trade.Symbol);
                        command.Parameters.AddWithValue("@time", trade.TradeTime);
                        command.Parameters.AddWithValue("@win", trade.IsWin);
                        command.Parameters.AddWithValue("@pnl", trade.PnL);
                        command.Parameters.AddWithValue("@spread", trade.Spread);
                        command.Parameters.AddWithValue("@gap", trade.Gap);
                        command.Parameters.AddWithValue("@volatility", trade.Volatility);

                        await command.ExecuteNonQueryAsync();
                    }

                    await transaction.CommitAsync();
                    Logger.Info($"成功插入 {trades.Count} 条历史交易记录");
                }
                catch
                {
                    await transaction.RollbackAsync();
                    throw;
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "批量插入交易历史失败");
                throw;
            }
        }

        /// <summary>
        /// 验证测试数据完整性
        /// </summary>
        public async Task<TestDataValidationResult> ValidateDataIntegrityAsync()
        {
            var result = new TestDataValidationResult();

            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                // 检查测试场景表
                result.ScenarioCount = await GetTableRowCountAsync(connection, "bonus_arbitrage_test_scenarios");

                // 检查历史交易表
                result.TradeHistoryCount = await GetTableRowCountAsync(connection, "arbitrage_trade_history");

                // 检查优化器配置表
                result.OptimizerConfigCount = await GetTableRowCountAsync(connection, "optimizer_configurations");

                // 检查测试结果表
                result.TestResultCount = await GetTableRowCountAsync(connection, "test_results");

                // 检查GARCH参数表
                result.GarchParameterCount = await GetTableRowCountAsync(connection, "garch_parameters");

                result.IsValid = result.ScenarioCount > 0; // 至少要有测试场景

                Logger.Info($"数据完整性验证: Scenarios={result.ScenarioCount}, " +
                           $"Trades={result.TradeHistoryCount}, Configs={result.OptimizerConfigCount}");

                return result;
            }
            catch (Exception ex)
            {
                Logger.Error(ex, "验证数据完整性失败");
                result.IsValid = false;
                result.ErrorMessage = ex.Message;
                return result;
            }
        }

        /// <summary>
        /// 获取特定表的行数
        /// </summary>
        private async Task<int> GetTableRowCountAsync(MySqlConnection connection, string tableName)
        {
            using var command = new MySqlCommand($"SELECT COUNT(*) FROM {tableName}", connection);
            var result = await command.ExecuteScalarAsync();
            return Convert.ToInt32(result);
        }

        /// <summary>
        /// 获取测试场景总数
        /// </summary>
        private async Task<int> GetRowCountAsync(MySqlConnection connection)
        {
            return await GetTableRowCountAsync(connection, "bonus_arbitrage_test_scenarios");
        }

        /// <summary>
        /// 重置自增ID
        /// </summary>
        public async Task ResetAutoIncrementAsync(string tableName)
        {
            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                using var command = new MySqlCommand($"ALTER TABLE {tableName} AUTO_INCREMENT = 1", connection);
                await command.ExecuteNonQueryAsync();

                Logger.Info($"重置表 {tableName} 的自增ID");
            }
            catch (Exception ex)
            {
                Logger.Error(ex, $"重置自增ID失败: {tableName}");
                throw;
            }
        }
    }

    /// <summary>
    /// 测试数据验证结果
    /// </summary>
    public class TestDataValidationResult
    {
        public bool IsValid { get; set; }
        public int ScenarioCount { get; set; }
        public int TradeHistoryCount { get; set; }
        public int OptimizerConfigCount { get; set; }
        public int TestResultCount { get; set; }
        public int GarchParameterCount { get; set; }
        public string ErrorMessage { get; set; }
    }
}